This data analysis uses open data from Mobilithek which is the dataset for trails in Hamburg, open data from Muenchen which is the dataset for trails in Muenchen, and open data of the European Union to render a map of the bike trails.
The question that interests me: Who have the higher density distribution of bike trail, Germany or Austria?
Initially, install all required dependencies. The specific version of SQLAlchemy is needed because SQLAlchemy 2.0 does not work with pandas yet. nbformat allows the use of the "notebook" formatter for the plot, others can not be rendered to HTML.
%pip install pandas
%pip install geopandas
%pip install matplotlib
%pip install contextily
%pip install plotly
%pip install 'SQLAlchemy==1.4.46'
%pip install nbformat
%pip install ipysheet
%pip install scipy
Requirement already satisfied: pandas in ./.venv/lib/python3.11/site-packages (2.0.1) Requirement already satisfied: python-dateutil>=2.8.2 in ./.venv/lib/python3.11/site-packages (from pandas) (2.8.2) Requirement already satisfied: pytz>=2020.1 in ./.venv/lib/python3.11/site-packages (from pandas) (2023.3) Requirement already satisfied: tzdata>=2022.1 in ./.venv/lib/python3.11/site-packages (from pandas) (2023.3) Requirement already satisfied: numpy>=1.21.0 in ./.venv/lib/python3.11/site-packages (from pandas) (1.24.3) Requirement already satisfied: six>=1.5 in ./.venv/lib/python3.11/site-packages (from python-dateutil>=2.8.2->pandas) (1.16.0) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: geopandas in ./.venv/lib/python3.11/site-packages (0.13.0) Requirement already satisfied: fiona>=1.8.19 in ./.venv/lib/python3.11/site-packages (from geopandas) (1.9.3) Requirement already satisfied: packaging in ./.venv/lib/python3.11/site-packages (from geopandas) (23.1) Requirement already satisfied: pandas>=1.1.0 in ./.venv/lib/python3.11/site-packages (from geopandas) (2.0.1) Requirement already satisfied: pyproj>=3.0.1 in ./.venv/lib/python3.11/site-packages (from geopandas) (3.5.0) Requirement already satisfied: shapely>=1.7.1 in ./.venv/lib/python3.11/site-packages (from geopandas) (2.0.1) Requirement already satisfied: attrs>=19.2.0 in ./.venv/lib/python3.11/site-packages (from fiona>=1.8.19->geopandas) (23.1.0) Requirement already satisfied: certifi in ./.venv/lib/python3.11/site-packages (from fiona>=1.8.19->geopandas) (2023.5.7) Requirement already satisfied: click~=8.0 in ./.venv/lib/python3.11/site-packages (from fiona>=1.8.19->geopandas) (8.1.3) Requirement already satisfied: click-plugins>=1.0 in ./.venv/lib/python3.11/site-packages (from fiona>=1.8.19->geopandas) (1.1.1) Requirement already satisfied: cligj>=0.5 in ./.venv/lib/python3.11/site-packages (from fiona>=1.8.19->geopandas) (0.7.2) Requirement already satisfied: munch>=2.3.2 in ./.venv/lib/python3.11/site-packages (from fiona>=1.8.19->geopandas) (3.0.0) Requirement already satisfied: python-dateutil>=2.8.2 in ./.venv/lib/python3.11/site-packages (from pandas>=1.1.0->geopandas) (2.8.2) Requirement already satisfied: pytz>=2020.1 in ./.venv/lib/python3.11/site-packages (from pandas>=1.1.0->geopandas) (2023.3) Requirement already satisfied: tzdata>=2022.1 in ./.venv/lib/python3.11/site-packages (from pandas>=1.1.0->geopandas) (2023.3) Requirement already satisfied: numpy>=1.21.0 in ./.venv/lib/python3.11/site-packages (from pandas>=1.1.0->geopandas) (1.24.3) Requirement already satisfied: six in ./.venv/lib/python3.11/site-packages (from munch>=2.3.2->fiona>=1.8.19->geopandas) (1.16.0) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: matplotlib in ./.venv/lib/python3.11/site-packages (3.7.1) Requirement already satisfied: contourpy>=1.0.1 in ./.venv/lib/python3.11/site-packages (from matplotlib) (1.0.7) Requirement already satisfied: cycler>=0.10 in ./.venv/lib/python3.11/site-packages (from matplotlib) (0.11.0) Requirement already satisfied: fonttools>=4.22.0 in ./.venv/lib/python3.11/site-packages (from matplotlib) (4.39.4) Requirement already satisfied: kiwisolver>=1.0.1 in ./.venv/lib/python3.11/site-packages (from matplotlib) (1.4.4) Requirement already satisfied: numpy>=1.20 in ./.venv/lib/python3.11/site-packages (from matplotlib) (1.24.3) Requirement already satisfied: packaging>=20.0 in ./.venv/lib/python3.11/site-packages (from matplotlib) (23.1) Requirement already satisfied: pillow>=6.2.0 in ./.venv/lib/python3.11/site-packages (from matplotlib) (9.5.0) Requirement already satisfied: pyparsing>=2.3.1 in ./.venv/lib/python3.11/site-packages (from matplotlib) (3.0.9) Requirement already satisfied: python-dateutil>=2.7 in ./.venv/lib/python3.11/site-packages (from matplotlib) (2.8.2) Requirement already satisfied: six>=1.5 in ./.venv/lib/python3.11/site-packages (from python-dateutil>=2.7->matplotlib) (1.16.0) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: contextily in ./.venv/lib/python3.11/site-packages (1.3.0) Requirement already satisfied: geopy in ./.venv/lib/python3.11/site-packages (from contextily) (2.3.0) Requirement already satisfied: matplotlib in ./.venv/lib/python3.11/site-packages (from contextily) (3.7.1) Requirement already satisfied: mercantile in ./.venv/lib/python3.11/site-packages (from contextily) (1.2.1) Requirement already satisfied: pillow in ./.venv/lib/python3.11/site-packages (from contextily) (9.5.0) Requirement already satisfied: rasterio in ./.venv/lib/python3.11/site-packages (from contextily) (1.3.7) Requirement already satisfied: requests in ./.venv/lib/python3.11/site-packages (from contextily) (2.31.0) Requirement already satisfied: joblib in ./.venv/lib/python3.11/site-packages (from contextily) (1.2.0) Requirement already satisfied: xyzservices in ./.venv/lib/python3.11/site-packages (from contextily) (2023.5.0) Requirement already satisfied: geographiclib<3,>=1.52 in ./.venv/lib/python3.11/site-packages (from geopy->contextily) (2.0) Requirement already satisfied: contourpy>=1.0.1 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (1.0.7) Requirement already satisfied: cycler>=0.10 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (0.11.0) Requirement already satisfied: fonttools>=4.22.0 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (4.39.4) Requirement already satisfied: kiwisolver>=1.0.1 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (1.4.4) Requirement already satisfied: numpy>=1.20 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (1.24.3) Requirement already satisfied: packaging>=20.0 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (23.1) Requirement already satisfied: pyparsing>=2.3.1 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (3.0.9) Requirement already satisfied: python-dateutil>=2.7 in ./.venv/lib/python3.11/site-packages (from matplotlib->contextily) (2.8.2) Requirement already satisfied: click>=3.0 in ./.venv/lib/python3.11/site-packages (from mercantile->contextily) (8.1.3) Requirement already satisfied: affine in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (2.4.0) Requirement already satisfied: attrs in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (23.1.0) Requirement already satisfied: certifi in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (2023.5.7) Requirement already satisfied: cligj>=0.5 in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (0.7.2) Requirement already satisfied: snuggs>=1.4.1 in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (1.4.7) Requirement already satisfied: click-plugins in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (1.1.1) Requirement already satisfied: setuptools in ./.venv/lib/python3.11/site-packages (from rasterio->contextily) (59.6.0) Requirement already satisfied: charset-normalizer<4,>=2 in ./.venv/lib/python3.11/site-packages (from requests->contextily) (3.1.0) Requirement already satisfied: idna<4,>=2.5 in ./.venv/lib/python3.11/site-packages (from requests->contextily) (3.4) Requirement already satisfied: urllib3<3,>=1.21.1 in ./.venv/lib/python3.11/site-packages (from requests->contextily) (1.26.16) Requirement already satisfied: six>=1.5 in ./.venv/lib/python3.11/site-packages (from python-dateutil>=2.7->matplotlib->contextily) (1.16.0) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: plotly in ./.venv/lib/python3.11/site-packages (5.14.1) Requirement already satisfied: tenacity>=6.2.0 in ./.venv/lib/python3.11/site-packages (from plotly) (8.2.2) Requirement already satisfied: packaging in ./.venv/lib/python3.11/site-packages (from plotly) (23.1) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: SQLAlchemy==1.4.46 in ./.venv/lib/python3.11/site-packages (1.4.46) Requirement already satisfied: greenlet!=0.4.17 in ./.venv/lib/python3.11/site-packages (from SQLAlchemy==1.4.46) (2.0.2) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: nbformat in ./.venv/lib/python3.11/site-packages (5.8.0) Requirement already satisfied: fastjsonschema in ./.venv/lib/python3.11/site-packages (from nbformat) (2.16.3) Requirement already satisfied: jsonschema>=2.6 in ./.venv/lib/python3.11/site-packages (from nbformat) (4.17.3) Requirement already satisfied: jupyter-core in ./.venv/lib/python3.11/site-packages (from nbformat) (5.3.0) Requirement already satisfied: traitlets>=5.1 in ./.venv/lib/python3.11/site-packages (from nbformat) (5.9.0) Requirement already satisfied: attrs>=17.4.0 in ./.venv/lib/python3.11/site-packages (from jsonschema>=2.6->nbformat) (23.1.0) Requirement already satisfied: pyrsistent!=0.17.0,!=0.17.1,!=0.17.2,>=0.14.0 in ./.venv/lib/python3.11/site-packages (from jsonschema>=2.6->nbformat) (0.19.3) Requirement already satisfied: platformdirs>=2.5 in ./.venv/lib/python3.11/site-packages (from jupyter-core->nbformat) (3.5.0) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: ipysheet in ./.venv/lib/python3.11/site-packages (0.7.0) Requirement already satisfied: ipywidgets<9.0,>=7.5.0 in ./.venv/lib/python3.11/site-packages (from ipysheet) (8.0.6) Requirement already satisfied: ipykernel>=4.5.1 in ./.venv/lib/python3.11/site-packages (from ipywidgets<9.0,>=7.5.0->ipysheet) (6.22.0) Requirement already satisfied: ipython>=6.1.0 in ./.venv/lib/python3.11/site-packages (from ipywidgets<9.0,>=7.5.0->ipysheet) (8.13.1) Requirement already satisfied: traitlets>=4.3.1 in ./.venv/lib/python3.11/site-packages (from ipywidgets<9.0,>=7.5.0->ipysheet) (5.9.0) Requirement already satisfied: widgetsnbextension~=4.0.7 in ./.venv/lib/python3.11/site-packages (from ipywidgets<9.0,>=7.5.0->ipysheet) (4.0.7) Requirement already satisfied: jupyterlab-widgets~=3.0.7 in ./.venv/lib/python3.11/site-packages (from ipywidgets<9.0,>=7.5.0->ipysheet) (3.0.7) Requirement already satisfied: comm>=0.1.1 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (0.1.3) Requirement already satisfied: debugpy>=1.6.5 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (1.6.7) Requirement already satisfied: jupyter-client>=6.1.12 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (8.2.0) Requirement already satisfied: jupyter-core!=5.0.*,>=4.12 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (5.3.0) Requirement already satisfied: matplotlib-inline>=0.1 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (0.1.6) Requirement already satisfied: nest-asyncio in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (1.5.6) Requirement already satisfied: packaging in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (23.1) Requirement already satisfied: psutil in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (5.9.5) Requirement already satisfied: pyzmq>=20 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (25.0.2) Requirement already satisfied: tornado>=6.1 in ./.venv/lib/python3.11/site-packages (from ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (6.3.1) Requirement already satisfied: backcall in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.2.0) Requirement already satisfied: decorator in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (5.1.1) Requirement already satisfied: jedi>=0.16 in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.18.2) Requirement already satisfied: pickleshare in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.7.5) Requirement already satisfied: prompt-toolkit!=3.0.37,<3.1.0,>=3.0.30 in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (3.0.38) Requirement already satisfied: pygments>=2.4.0 in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (2.15.1) Requirement already satisfied: stack-data in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.6.2) Requirement already satisfied: pexpect>4.3 in ./.venv/lib/python3.11/site-packages (from ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (4.8.0) Requirement already satisfied: parso<0.9.0,>=0.8.0 in ./.venv/lib/python3.11/site-packages (from jedi>=0.16->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.8.3) Requirement already satisfied: python-dateutil>=2.8.2 in ./.venv/lib/python3.11/site-packages (from jupyter-client>=6.1.12->ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (2.8.2) Requirement already satisfied: platformdirs>=2.5 in ./.venv/lib/python3.11/site-packages (from jupyter-core!=5.0.*,>=4.12->ipykernel>=4.5.1->ipywidgets<9.0,>=7.5.0->ipysheet) (3.5.0) Requirement already satisfied: ptyprocess>=0.5 in ./.venv/lib/python3.11/site-packages (from pexpect>4.3->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.7.0) Requirement already satisfied: wcwidth in ./.venv/lib/python3.11/site-packages (from prompt-toolkit!=3.0.37,<3.1.0,>=3.0.30->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.2.6) Requirement already satisfied: executing>=1.2.0 in ./.venv/lib/python3.11/site-packages (from stack-data->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (1.2.0) Requirement already satisfied: asttokens>=2.1.0 in ./.venv/lib/python3.11/site-packages (from stack-data->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (2.2.1) Requirement already satisfied: pure-eval in ./.venv/lib/python3.11/site-packages (from stack-data->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (0.2.2) Requirement already satisfied: six in ./.venv/lib/python3.11/site-packages (from asttokens>=2.1.0->stack-data->ipython>=6.1.0->ipywidgets<9.0,>=7.5.0->ipysheet) (1.16.0) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: scipy in ./.venv/lib/python3.11/site-packages (1.11.0) Requirement already satisfied: numpy<1.28.0,>=1.21.6 in ./.venv/lib/python3.11/site-packages (from scipy) (1.24.3) Note: you may need to restart the kernel to use updated packages.
Create a pandas or geopandas dataframe using the local sqlite files.
import pandas as pd
import geopandas as gpd
hamburg = gpd.read_file('project/data/hamburg.sqlite', driver='SQLite', spatialite=True, layer='hamburg')
muenchen = gpd.read_file('project/data/muenchen.sqlite', driver='SQLite', spatialite=True, layer='muenchen')
tirol = pd.read_sql_table('tirol', 'sqlite:///project/data/tirol.sqlite')
There are three datasets one of all trails from Hamburg, one of all trails from Muenchen and on contains all trails from Tirol. To answer our initial question, we use plotly and contextily to draw a scatterplot of all bike trails within our datasets, overlaying it on a map from OpenStreetMap.
The bike trails will be colored based on the name, allowing us to see what different bike trails are there.
import contextily as cx
hamburg_gdf_wm = hamburg.to_crs(epsg=3857)
ax = hamburg_gdf_wm.plot(column='routenname', figsize=(10, 10), alpha=0.5,legend=True)
leg = ax.get_legend()
leg.set_bbox_to_anchor((1.3, 1.0))
cx.add_basemap(ax)
ax.set_axis_off()
import contextily as cx
muenchen_gdf_wm = muenchen.to_crs(epsg=3857)
ax = muenchen_gdf_wm.plot(column='strassenna',figsize=(10, 10), alpha=0.5, legend=True)
leg = ax.get_legend()
leg.set_bbox_to_anchor((1.6, 1.0))
cx.add_basemap(ax)
ax.set_axis_off()
import plotly.io as pio
import plotly.express as px
pio.renderers.default = "notebook"
fig = px.scatter_mapbox(tirol,
lat="latitude",
lon="longitude",
hover_name="name",
hover_data=["name"],
color="name",
zoom=5,
height=800,
width=1200)
fig.update_layout(mapbox_style="open-street-map")
fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
fig.show()
(unfortunately not rendered)
| Name of dataset: | Number of trails: |
|---|---|
| Hamburg | 19 |
| Muenchen | 10 |
| Tirol | 41 |
from ipysheet import sheet, cell
import math
total_trails_hamburg = hamburg['routenname'].count()
total_trails_muenchen = muenchen['strassenna'].count()
total_trails_tirol = tirol['name'].count()
sheet_test = sheet(column_headers=['Name of dataset:','Number of trails:'])
cell10 = cell(0, 0, 'Hamburg')
cell20 = cell(1, 0, 'Muenchen')
cell30 = cell(2, 0, 'Tirol')
cell11 = cell(0, 1, total_trails_hamburg)
cell21 = cell(1, 1, total_trails_muenchen)
cell31 = cell(2, 1, total_trails_tirol)
sheet_test
Sheet(cells=(Cell(column_end=0, column_start=0, row_end=0, row_start=0, type='text', value='Hamburg'), Cell(co…
Austria (Tyrol) seems to be the better place for cycling, as the density of bike lanes is higher than in Germany (Hamburg, Munich). For a more meaningful conclusion, we need more data from different regions of the two countries to make a better comparison.
It might be interesting to use the API directly from Komoot to get better and more data about bike lanes in Austria and Germany. Here is an exciting article how to do that: https://medium.com/@franzke.christoph/create-your-own-komoot-dashboard-6a0cd35ab89